package org.tsinghua.air.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class DbConnection {
	public final static String DRIVER = "org.hsqldb.jdbcDriver";
	public final static String URL = "jdbc:hsqldb:data/db";
	// public final static String DRIVER =
	// "org.apache.derby.jdbc.EmbeddedDriver";
	// public final static String URL = "jdbc:derby:db;create=true";

	private static Connection cnn = null;

	private DbConnection() {
	}

	public static Connection getConnection() throws ClassNotFoundException, SQLException {
		if (cnn == null) {
			synchronized (DbConnection.class) {
				if (cnn == null) {
					Class.forName(DRIVER);
					cnn = DriverManager.getConnection(URL, "sa", "");
				}
			}
		}

		return cnn;
	}

	public static void closeConnection() {
		try {
			if (cnn != null && !cnn.isClosed())
				cnn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static String[] getColumnNames(String table) throws Exception {
		Connection cn = getConnection();
		if (cn != null && !cn.isClosed()) {
			Statement statement = cn.createStatement();
			ResultSet results = statement.executeQuery("SELECT * FROM " + table);
			ResultSetMetaData metadata = results.getMetaData();
			String[] col = new String[metadata.getColumnCount()];
			for (int i = 1; i <= col.length; i++) {
				col[i - 1] = metadata.getColumnName(i);
			}
			statement.close();
			return col;
		} else {
			return null;
		}
	}

	/**
	 * one more empty column than a table record.
	 * @param table
	 * @return
	 * @throws Exception
	 */
	public static List<String[]> getData(String table) throws Exception {
		List<String[]> list = new ArrayList<String[]>();
		Connection cn = getConnection();
		if (cn != null && !cn.isClosed()) {
			Statement statement = cn.createStatement();
			ResultSet results = statement.executeQuery("SELECT * FROM " + table);
			while (results.next()) {
				String[] col = new String[results.getMetaData().getColumnCount() + 1];
				col[0]=null;
				for (int i = 1; i < col.length; i++) {
					if (results.getString(i) != null)
						col[i] = String.valueOf(results.getString(i));
				}
				list.add(col);
			}
			statement.close();
		}
		return list;
	}
}
